package com.yuanqi.repository;

import com.yuanqi.domain.RetailInventoryAdjustment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;

/**
 * 零售库存调整记录数据访问层
 */
@Repository
public interface RetailInventoryAdjustmentRepository extends JpaRepository<RetailInventoryAdjustment, Long> {
    
    /**
     * 根据零售商品ID查找库存调整记录
     */
    List<RetailInventoryAdjustment> findByRetailProductIdOrderByAdjustmentDateDesc(Long retailProductId);
    
    /**
     * 根据调整类型查找记录
     */
    List<RetailInventoryAdjustment> findByAdjustmentTypeOrderByAdjustmentDateDesc(String adjustmentType);
    
    /**
     * 根据日期范围查找记录
     */
    @Query("SELECT ria FROM RetailInventoryAdjustment ria WHERE ria.adjustmentDate BETWEEN :startDate AND :endDate ORDER BY ria.adjustmentDate DESC")
    List<RetailInventoryAdjustment> findByAdjustmentDateBetween(@Param("startDate") LocalDate startDate, 
                                                               @Param("endDate") LocalDate endDate);
    
    /**
     * 根据零售商品ID和日期范围查找记录
     */
    @Query("SELECT ria FROM RetailInventoryAdjustment ria WHERE ria.retailProductId = :retailProductId AND ria.adjustmentDate BETWEEN :startDate AND :endDate ORDER BY ria.adjustmentDate DESC")
    List<RetailInventoryAdjustment> findByRetailProductIdAndAdjustmentDateBetween(@Param("retailProductId") Long retailProductId,
                                                                                  @Param("startDate") LocalDate startDate,
                                                                                  @Param("endDate") LocalDate endDate);
    
    /**
     * 统计今日库存调整记录数
     */
    @Query("SELECT COUNT(ria) FROM RetailInventoryAdjustment ria WHERE DATE(ria.adjustmentDate) = CURRENT_DATE")
    Long countTodayAdjustments();
    
    /**
     * 统计本月库存调整记录数
     */
    @Query("SELECT COUNT(ria) FROM RetailInventoryAdjustment ria WHERE YEAR(ria.adjustmentDate) = YEAR(CURRENT_DATE) AND MONTH(ria.adjustmentDate) = MONTH(CURRENT_DATE)")
    Long countMonthAdjustments();
    
    /**
     * 统计今日入库数量
     */
    @Query("SELECT COALESCE(SUM(ria.quantityChange), 0) FROM RetailInventoryAdjustment ria WHERE ria.quantityChange > 0 AND DATE(ria.adjustmentDate) = CURRENT_DATE")
    Long sumTodayInbound();
    
    /**
     * 统计今日出库数量
     */
    @Query("SELECT COALESCE(SUM(ABS(ria.quantityChange)), 0) FROM RetailInventoryAdjustment ria WHERE ria.quantityChange < 0 AND DATE(ria.adjustmentDate) = CURRENT_DATE")
    Long sumTodayOutbound();
    
    /**
     * 统计本月入库数量
     */
    @Query("SELECT COALESCE(SUM(ria.quantityChange), 0) FROM RetailInventoryAdjustment ria WHERE ria.quantityChange > 0 AND YEAR(ria.adjustmentDate) = YEAR(CURRENT_DATE) AND MONTH(ria.adjustmentDate) = MONTH(CURRENT_DATE)")
    Long sumMonthInbound();
    
    /**
     * 统计本月出库数量
     */
    @Query("SELECT COALESCE(SUM(ABS(ria.quantityChange)), 0) FROM RetailInventoryAdjustment ria WHERE ria.quantityChange < 0 AND YEAR(ria.adjustmentDate) = YEAR(CURRENT_DATE) AND MONTH(ria.adjustmentDate) = MONTH(CURRENT_DATE)")
    Long sumMonthOutbound();
}
